87

Build Your Own Virtual Filing Cabinet

87

STEP 6

Sub PickFile(FileName)

  Dim fldr As FileDialog

  Dim sItem As String

  Set fldr =​ Application.FileDialog(msoFileDialogSaveAs)

  With fldr

    .Title =​ “Save as”

    .InitialFileName =​ “test.xlsx”

    .FilterIndex =​ 1

    If .Show <> 0 Then

      FileName =​ .SelectedItems(1)

      ActiveWorkbook.SaveAs FileName:=​.SelectedItems(1)

    End If

  End With

End Sub

5.2  INVOKING THE CODE

Now that the program is written, you will need a mechanism to invoke it. While there

are many ways to do this, a quick and easy way is to add a rectangular shape on the

sheet that doubles as a button.

FIGURE 5.1  Invoke macro from rectangle shape.

Below is a sample output.

Name

Type

Size

Path

Last Modified on

ACS - Augmenting Common Sense with Spreadsheets.docx

FILE

1081314 C:�sers\deepa\Documents\My Creativity\ACS - Augmenting Common Sense with Spreadsheets.docx

9/11/2020 12:19

address-information-api.pdf

FILE

494673 C:�sers\deepa\Documents\My Creativity\address-information-api.pdf

4/11/2020 10:24

Algorithm for Web Scraping using VBA.docx

FILE

732357 C:�sers\deepa\Documents\My Creativity\Algorithm for Web Scraping using VBA.docx

4/13/2020 18:24

Algorithm for Web Scraping using VBA.pdf

FILE

350825 C:�sers\deepa\Documents\My Creativity\Algorithm for Web Scraping using VBA.pdf

4/13/2020 18:26

Aparajita Drivers Licence v1.jpg

FILE

223874 C:�sers\deepa\Documents\My Creativity\Aparajita Drivers Licence v1.jpg

3/24/2020 13:05

artondenim.pdf

FILE

139315 C:�sers\deepa\Documents\My Creativity\artondenim.pdf

8/23/2020 14:59

bookmark.htm

FILE

61669 C:�sers\deepa\Documents\My Creativity\bookmark.htm

7/1/2020 13:44

Camera excel with screenshot processing.xlsm

FILE

60534 C:�sers\deepa\Documents\My Creativity\Camera excel with screenshot processing.xlsm

5/8/2019 14:21

CheckWebPage.xlsm

FILE

52262 C:�sers\deepa\Documents\My Creativity\CheckWebPage.xlsm

4/10/2020 9:48

5.3  CONCLUSION

This chapter helps you understand files and how to work with them in Excel VBA.

Manipulating files through a program enables you to maximize the automation

opportunities in Excel and use them for several business scenarios.